Conditional Formatting in Excel

Excel represents the go-to solution for data storage and representation for most business teams. Whether you work in finance, human resources, or marketing, the amount of data is definitely going to determine your analysis and visualization tasks. Thankfully, Excel offers a feature known as conditional formatting, which helps users organize and manage their whole data set in a few clicks. To make the most out of this rule-based feature, you must become familiar with how to use it correctly.

In this complete guide, you’ll learn what conditional formatting is and the many uses it offers in Excel. You’ll learn the basics of conditional formatting in Excel and also learn its more advanced uses when combined with IF or AND formulas. What is great about this feature in Excel is that it can be as basic or as complex as you need, depending on the level of customization your data requires. From setting multiple conditions to formatting rules to selecting which spreadsheet data it should apply to, conditional formatting is one of the most valuable features in Excel to boost productivity at all company levels.

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Conditional formatting is a spreadsheet application feature that allows users to format data following a set of built-in or customized conditions. The most common use is to highlight key information through color-coding or remove duplicate content in one click.

The conditions or rules you set will apply to a cell or cell range, which can include columns; this means that the data in the cell will automatically change to the value specified in the rule. For example, you can ask Excel to highlight (for example, in the color green) all cells that contain a numeric value “greater than” the amount specified. This type of condition will change the color format, but you can add other formatting aspects, such as the font size or style.

Conditional formatting changes the formatting of your data with conditions that act as “If/Then” statements. You set the condition of the rule to a specified cell range, and then determine the formatting style which will be applied to the cells that fulfill this rule. In other words, “If (data range) is (condition of rule), then (formatting style)” is applied.

Depending on the complexity of the rule you would like to apply, from basic to more advanced, you can create the following types of conditions in Excel:

Google Sheet allows you to make data more readable by changing the color of cells based on their value. Here's how to use conditional formatting in Google Sheets.

We’ll start by showing the most popular conditional formatting types in Excel based on their ease of use and applicability. Note that the examples shown are based on the Excel desktop app, since you can only apply conditional formatting in this version. The cloud-based application in Office 365 will only let you view, but not edit or create, new rules.

This type of conditional formatting is one of the most commonly used in Excel since it helps to highlight key information and to structure your data visually so that it becomes more digestible, especially when dealing with large datasets.

For example, let’s say I work in the sales department of an international car dealership, and I need to report which car models have yielded above the expected profit margin. This is how to apply conditional formatting to change the cell color based on the cell value:

As you will be able to see, Excel provides a full range of color formatting rules that can be applied to any data type, not only numbers. Moreover, the last highlighting rule applies to duplicate values, which is useful for later removal or simply as a way of quickly visualizing repeat information. You will find our article on How to Find and Remove Duplicates in Excel very useful.

In the same way that applying a conditional formatting rule to a cell or cell range, you can also apply it to a column - simply highlight the entire column before using the feature. Here’s how to conditionally format a column in Excel using the “Top/Bottom Rules”.

Now that we have seen the basic uses of conditional formatting in Excel, let’s explore more advanced options that will let you customize the rules according to your specific needs.

So far, we have seen examples of using conditional formatting in Excel with built-in rules, such as “Highlight Cells Rules” or “Top/Bottom Rules”. But what if you want to apply more than one rule at the same time? Luckily, you are already using Excel and this means that you have a range of formulae to apply. Let’s see how the IF formula can be used with conditional formatting.

If you would like to conditionally format cells that contain duplicate values, you can use the built-in rule in Conditional formatting > Highlight Cells Rules > Duplicate Values…

However, you may also want to conditionally format the other columns or rows containing those duplicate values. The =COUNTIF formula in Excel is used to count the number of cells within a selected range that meet a specified criterion.

Let’s see how the =COUNTIF formula works by explaining its parameters:

This is how you can highlight duplicate values using the =COUNTIF formula in Excel:

Once you click “OK”, you should be able to see all 2006 duplicates in yellow.

We have now used an Excel formula, =COUNTIF, with the “Duplicate Cells” conditional formatting rule. Let’s now illustrate how multiple conditions can be applied based on the type of data and the type of formatting we would like to apply.

The AND formula is also a logical function that tests the conditions to return one of the true/false values, depending on whether they are met or not.

Following the use case, let’s say that I want to conditionally format the VIN (Vehicle Identification Number) values or registered cars from 2006 onwards and that have yielded a profit value above the minimum, $1,153.00.

There are many ways to combine multiple columns into a single column in Excel. Here's how to do it without losing any data

What if, instead of applying a rule to a given value, you want to base it on the value of another cell in the spreadsheet? This cell value will act as the fixed value when applying the highlighting rule. If we continue with the car profit margin example, the highlight was included in the formatting when a numeric value exceeded the expected profit value. However, since the profit margin value might change over time, we might prefer to create a rule that strictly depends on a controlled value. This is how you can conditionally format an Excel cell based on another cell.

Any value above the one specified will now be highlighted according to this conditional formatting rule. Now you know how to create a dynamic rule, as the minimum profit changes, the rule will adjust to the fixed value.

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conditional formatting in Excel has proven to be a versatile tool. As this guide has shown, the built-in rules already save you time on repetitive daily tasks by performing editing actions in bulk, such as color-coding or removing duplicate cells. If you would like Excel to perform more advanced actions, where multiple conditions or more sophisticated criteria need to be applied, it will also allow you to combine these with formulae.

You can now save time in cleaning and organizing large or smaller datasets. Hopefully, this guide has shown that an Excel spreadsheet goes beyond a data storage system and represents an all-around reference for business tasks related to planning, organizing, and decision-making.

If you would like to try conditional formatting functionality in Google Sheets, read our article on How to Use Conditional Formatting in Google Sheets.

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.